# __author: Rachel章
# data: 2023/4/20
# desc: 契望成数据库工具类
import pymysql
import re

from common.config import *


class QwcMysqlDb():
    # 环境，数据库表名，用户名，密码都是变量
    def __init__(self, env):
        host_db = getconf(env, "qwc_host_db")
        user_db = getconf(env, "qwc_user_db")
        password = getconf(env, "qwc_password")
        port = getconf(env, "qwc_port")
        try:
            self.conn = pymysql.connect(host=host_db, user=user_db, password=password, port=int(port), charset="utf8")
            self.cur = self.conn.cursor(pymysql.cursors.DictCursor)
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))

    # 单条数据操作,查询
    def query(self, conndition):
        self.cur.execute(conndition)
        data = self.cur.fetchall()
        self.conn.commit()
        print(data)
        return data

    # 单条数据操作，包括删除，更新
    def execute(self, conndition):
        try:
            self.cur.execute(conndition)
            self.conn.commit()
            # print ("执行了吗")
            result = {"code": "0000", "message": "执行通用操作成功", "data": []}
        except pymysql.Error as e:
            print(e)
            self.conn.rollback()
            result = {"code": "9999", "message": "执行通用操作异常", "data": []}
            # print("操作数据库失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        print(result)
        return result

    # 查询表中单条数据
    def fetch_one(self, conndition):
        try:
            rows_affect = self.cur.execute(conndition)
            if rows_affect > 0:
                results = self.cur.fetchone()
                result = {"code": "0000", "message": "执行单条查询成功", "data": results}
            else:
                result = {"code": "0000", "message": "执行单条查询成功", "data": []}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "message": "执行单条查询异常", "data": []}
            print("查询单条数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        print(result)
        return result

    # 查询表中多条数据
    def fetch_all(self, conndition):
        try:
            rows_affect = self.cur.execute(conndition)
            if rows_affect > 0:
                self.cur.scroll(0, mode='absolute')  # 鼠标光标回到初始位置
                results = self.cur.fetchall()
                result = {"code": "0000", "message": "执行批量查询成功", "data": results}
            else:
                result = {"code": "0000", "message": "执行批量查询成功", "data": []}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "message": "执行批量查询异常", "data": []}
            print("查询多条数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        select = result["data"]
        selectall = str(select).replace('(', '').replace(')', '').replace("',", '').replace("'", "").replace(" ", "")
        print(selectall)
        return selectall

    # 插入数据
    def insert(self, conndition, params):
        try:
            results = self.cur.executemany(conndition, params)  # 返回插入的数据条数
            self.conn.commit()
            result = {"code": "0000", "message": "执行批量插入成功", "data": results}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "message": "执行批量插入异常", "data": []}
            print("插入数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        return result

    # 数据库关闭
    def __del_(self):
        if self.cur != None:
            self.cur.close()
        if self.conn != None:
            self.conn.close()


if __name__ == "__main__":
    # examples:
    qwcDb = QwcMysqlDb("test")
    result = qwcDb.query("SELECT card_code from flashmall_member.org_member where phone like '%13%';")  #查询
    print(result)
    result = QwcMysqlDb("test").execute("update flashmall_member.org_member set phone='16601964207' where phone = '16601964206';") #更新
    result = QwcMysqlDb("test").fetch_one("SELECT card_code from flashmall_member.org_member where register_channel_type = 2;")
    result = QwcMysqlDb("test").fetch_all("SELECT card_code from flashmall_member.org_member where register_channel_type = 2;")
    result = QwcMysqlDb("test").insert("'INSERT INTO student values(%s,%s)'",params=[(9,"ab"),(6,"abcd"),(7,"abcde")])
